CREATE DATABASE RA;
GO

USE RA;
GO

CREATE TABLE EmployeeRole (
	ID INT PRIMARY KEY,
	RoleName NVARCHAR(30)
);
GO

INSERT INTO EmployeeRole VALUES (1, 'Manager');
INSERT INTO EmployeeRole VALUES (2, 'Host');
INSERT INTO EmployeeRole VALUES (3, 'Waiter');
INSERT INTO EmployeeRole VALUES (4, 'Cook');
INSERT INTO EmployeeRole VALUES (5, 'BusBoy');

CREATE TABLE TableStatus (
	ID INT PRIMARY KEY,
	StatusName NVARCHAR(30)
);

INSERT INTO TableStatus VALUES (1, 'Available');
INSERT INTO TableStatus VALUES (2, 'Reserved');
INSERT INTO TableStatus VALUES (3, 'Waiting');
INSERT INTO TableStatus VALUES (4, 'Occupied');
INSERT INTO TableStatus VALUES (5, 'Dirty');
INSERT INTO TableStatus VALUES (6, 'Cleaning');

CREATE TABLE OrderStatus (
	ID INT PRIMARY KEY,
	StatusName NVARCHAR(30)
);

INSERT INTO OrderStatus VALUES (1, 'Waiting');
INSERT INTO OrderStatus VALUES (2, 'Cooking');
INSERT INTO OrderStatus VALUES (3, 'Ready');
INSERT INTO OrderStatus VALUES (4, 'Cancelled');
INSERT INTO OrderStatus VALUES (5, 'Delivered');

CREATE TABLE Employees (
	ID INT PRIMARY KEY IDENTITY(1,1),
	EmployeeCode NVARCHAR(30) UNIQUE NOT NULL,
	FirstName NVARCHAR(50),
	LastName NVARCHAR(50),
	Pass NVARCHAR(50),
	[Role] INT REFERENCES EmployeeRole(ID) NOT NULL,
	Note NVARCHAR(MAX)
);

CREATE TABLE Categories (
	ID INT PRIMARY KEY IDENTITY(1,1),
	[Name] NVARCHAR(50) NOT NULL
);

CREATE TABLE Products (
	ID INT PRIMARY KEY IDENTITY(1,1),
	[Name] NVARCHAR(50) NOT NULL,
	Category INT REFERENCES Categories(ID),	
	MenuPrice MONEY NOT NULL,
	[Image] VARBINARY(MAX),
	Available BIT NOT NULL,
	Note NVARCHAR(MAX)
);

CREATE TABLE Tables (
	ID INT PRIMARY KEY IDENTITY(1,1),
	TableName NVARCHAR(30) UNIQUE NOT NULL,
	NumOfSeats INT CHECK(NumOfSeats > 0) NOT NULL,
	PosX FLOAT DEFAULT 0 NOT NULL,
	PosY FLOAT DEFAULT 0 NOT NULL,
	[Status] INT REFERENCES TableStatus(ID) NOT NULL DEFAULT (1),
	Note NVARCHAR(MAX)
);

CREATE TABLE [Sessions] (
	ID INT PRIMARY KEY IDENTITY(1,1),
	[Table] INT REFERENCES Tables(ID) NOT NULL,
	StartTime DATETIME NOT NULL DEFAULT GETDATE(),
	FinishTime DATETIME,
	Note NVARCHAR(MAX)
);

CREATE TABLE OrderItems (
	ID INT PRIMARY KEY IDENTITY(1,1),
	Session INT REFERENCES [Sessions](ID) NOT NULL,	
	Product INT REFERENCES Products(ID) NOT NULL,
	Price MONEY NOT NULL,
	Quantity INT CHECK(Quantity > 0) NOT NULL,
	[Status] INT REFERENCES OrderStatus(ID) NOT NULL DEFAULT(1),
	Cook INT REFERENCES Employees(ID),
	PlacingWaiter INT REFERENCES Employees(ID),
	DeliverWaiter INT REFERENCES Employees(ID),
	PlacingTime DATETIME NOT NULL DEFAULT GETDATE(),
	FinishedTime DATETIME,
	Note NVARCHAR(MAX)
);

CREATE TABLE CleanRequests (
	ID INT PRIMARY KEY IDENTITY(1,1),
	BusBoy INT REFERENCES Employees(ID),
	[Table] INT REFERENCES Tables(ID),
	CleaningTime DATETIME DEFAULT GETDATE()
);

CREATE TABLE ReservedPeriods (
	ID INT PRIMARY KEY IDENTITY(1,1),
	[Table] INT REFERENCES Tables(ID) NOT NULL,
	StartTime DATETIME NOT NULL,
	FinishTime DATETIME,
	Note NVARCHAR(MAX)
);